SSIS - convert dbf to sql server

[Garima12]

My goal is to convert dbf to sql server database using SSIS. The package will run periodically say once in 24 hrs. and again update the data in sql server from dbf file. For this is need to create package and schedule a job using job agent. As I am new to SSIS, my question is like I don't want to overwrite the records everytime the package runs, it means I m required to check which records are alredy existing in the database. So do I need to program for that in SSIS? Also, can someone tell in brief that what all I need to do accomplish this task. Please let me know the steps in short.
thks

[ck9663]

I've never used SSIS yet so I'll just describe how I would do it if am in your shoes.

1. Upload the DBF into a monthly table. Name the table appropriately. This will be handy in tracing if there are errors and you need to go back to the actual data that you uploaded.

2. Create a stored proc that will handle your insert-if-not-exist requirement. You might want to add a field on your MASTER table to indicate when the record was inserted. A string YYYYMM could be fine or a complete date.

3. It'll be up to you if you will delete the monthly table after processing or not. I'd keep for a while. It's easier to drop tables than to recreate them.

-- CK

[Garima12]

thks for answering question. suppose, I don't want to check for existing entries and everytime drop the existing table and create new one,so how long will it take to create such package? what do u think?

[ck9663]

For sure it would be faster than the code with checking for existence.

There are processing that would require that you check for existence. There are others that you can just truncate the existing table and load the new data. There are others that you need to create a table every month, and check a master table for existence and add those that are not existing on the same master table.

Each of these have their own pros and cons. That'll be up to you and would depend, greatly, on your process flow.

-- CK